﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Weight.Model;
using System.Data;
using Dapper;
using Weight.Base;
using Dapper.Contrib.Extensions;
using System.Data.SqlClient;
using Weight.Model.ResultEntity;

namespace Weight.Dal.Contract
{
    public  class DalContractTicket
    {
        /// <summary>
        /// 获取订单列表
        /// </summary>
        /// <param name="mainId"></param>
        /// <param name="isStart"></param>
        /// <returns></returns>
        public  List<ContractTicketRes> ListByMainId(string mainId,string material, string useStatus)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = @" select  * ,
                    (select sum(RechargeVal) from  ContractTicketRecharge  where TicketId=t.Id) as Amount,
                    (select sum(ConsumeVal) from  ContractTicketConsume  where TicketId=t.Id) as ConsumeAmount,
                    (select sum(RechargeMoney) from  ContractTicketRecharge  where TicketId=t.Id ) as Money,
                    (select sum(ConsumeMoney) from  ContractTicketConsume   where TicketId=t.Id) as ConsumeMoney
                    from  ContractTicket t  where    MainId=@mainId and IsDel=@IsDel";
                if(!string.IsNullOrEmpty(useStatus))
                {
                    sql += " and  UseStatus = @useStatus ";
                }
                if (!string.IsNullOrEmpty(material))
                {
                    material = "%" + material + "%";
                    sql += " and Material like @material";
                }
                return conn.Query<ContractTicketRes>(sql, new { useStatus, material, mainId, IsDel="否" }).ToList();
            }
        }


        /// <summary>
        /// 获取订单列表
        /// </summary>
        /// <param name="isStart"></param>
        /// <returns></returns>
        public DataTable ListSelect(string contractNo,string customer, string material)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = @" select t.Id,  cust.Customer,cust.CardId,PhoneNum,Company,t.Type,t.Material,t.Spec,
                    t.Price,t.CreditVal,t.Mode,t.Sender,t.Receiver,
                    (select sum(RechargeVal) from  ContractTicketRecharge  where TicketId=t.Id) as Amount,
                    (select sum(ConsumeVal) from  ContractTicketConsume  where TicketId=t.Id) as ConsumeAmount,
                    (select sum(RechargeMoney) from  ContractTicketRecharge  where TicketId=t.Id ) as Money,
                    (select sum(ConsumeMoney) from  ContractTicketConsume   where TicketId=t.Id) as ConsumeMoney
                    from  ContractTicket t 
                    left join  ContractCustomer cust on t.MainId = cust.Id
                    where  UseStatus='执行' and  t.IsDel='否' and cust.IsDel='否' ";
                if(!string.IsNullOrEmpty(contractNo))
                {
                    sql += " and t.Id like '%"+ contractNo + "%'";
                }
                if(! string.IsNullOrEmpty(customer))
                {
                    sql += " and Customer like '%" + customer + "%'";
                }
                if (!string.IsNullOrEmpty(material))
                {
                    sql += " and Material like '%" + material + "%'";
                }

                DataTable dt = new DataTable();
                SqlDataAdapter dp = new SqlDataAdapter(sql, conn as SqlConnection);
                dp.Fill(dt);
                return dt;
            }
        }
        /// <summary>
        /// 添加订单
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Add(ContractTicketEntity entity, ContractTicketRechargeEntity rechargeEntity)
        {
            using(IDbConnection conn = SessionFactory.CreateConnection())
            {
                IDbTransaction tran =  conn.BeginTransaction();
                try
                {
                    conn.Insert(entity, tran);
                    if(rechargeEntity != null)
                    {
                        conn.Insert(rechargeEntity,tran);
                    }
                    tran.Commit();
                    return true;
                }
                catch(Exception ex)
                {
                    tran.Rollback();
                    return false;
                }
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Update(ContractTicketEntity entity, ContractTicketRechargeEntity chargeEntity)
        {
            using(IDbConnection conn = SessionFactory.CreateConnection())
            {
                IDbTransaction tran = conn.BeginTransaction();
                try
                {
                    conn.Update(entity, tran);
                    conn.Execute("delete from  ContractTicketRecharge where TicketId=@TicketId and Status=0 ", new { TicketId = entity.Id }, tran);
                    conn.Insert(chargeEntity,tran);
                    tran.Commit();
                    return true;
                }
                catch(Exception ex)
                {
                    tran.Rollback();
                    return false;
                }
              
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Del(string id)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = " update ContractTicket set IsDel=@IsDel where Id=@Id";
                return conn.Execute(sql,new { IsDel="是",Id=id }) > 0 ;
            }
        }

        /// <summary>
        /// 暂停
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Stop(string id)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = " update ContractTicket set UseStatus=@UseStatus where Id=@Id";
                return conn.Execute(sql, new { UseStatus = "暂停", Id = id }) > 0;
            }
        }

        /// <summary>
        /// 执行
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Start(string id)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = " update ContractTicket set UseStatus=@UseStatus where Id=@Id";
                return conn.Execute(sql, new { UseStatus = "执行", Id = id }) > 0;
            }
        }

        /// <summary>
        /// 获取订单信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ContractTicketRes GetById(string id)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = @" select  *,
                 (select sum(RechargeVal) from  ContractTicketRecharge  where TicketId=@Id  and Status=0 ) as Amount,
                 (select sum(RechargeMoney) from  ContractTicketRecharge  where TicketId=@Id and Status=0 ) as Money
                from  ContractTicket  where Id=@Id";
                return conn.Query<ContractTicketRes>(sql, new { Id = id }).FirstOrDefault();
            }
        }

        /// <summary>
        /// 判断订单是否已经存在
        /// </summary>
        /// <param name="material"></param>
        /// <param name="mId"></param>
        /// <returns></returns>
        public bool IsExist(string material, string mId)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = " select count(1) from  ContractTicket where Material=@Material and MainId=@MainId";
                return conn.ExecuteScalar<int>(sql, new { Material = material, MainId = mId }) > 0;
            }
        }

        /// <summary>
        /// 判断订单是否已经存在
        /// </summary>
        /// <param name="material"></param>
        /// <param name="mId"></param>
        /// <returns></returns>
        public bool IsExist(string material, string mId,string id)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = " select count(1) from  ContractTicket where Material=@Material and MainId=@MainId and Id<>@Id";
                return conn.ExecuteScalar<int>(sql, new { Material = material, MainId = mId ,Id=id}) > 0;
            }
        }

        /// <summary>
        /// 获取图表分析数据
        /// </summary>
        /// <param name="fields"></param>
        /// <param name="where"></param>
        /// <param name="group"></param>
        /// <returns></returns>
        public DataTable GetChartPieData(string fields,string where ,string group)
        {
            StringBuilder sqlBuider = new StringBuilder();
            sqlBuider.Append(" select ").Append(fields).Append(" from  ContractTicket  t  ").Append(where).Append(" ").Append(group);
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                DataSet dataSet = new DataSet();
                SqlDataAdapter dp = new SqlDataAdapter(sqlBuider.ToString(), conn as SqlConnection);
                dp.Fill(dataSet);
                return dataSet.Tables[0];
            }
        }
    }
}
